/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.struts.dao;

import com.myapp.struts.beans.AllocationBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author pavan
 */
public class AllocationDao {

    private double QtyToBeAllocated;
    private double QtyToBeAllocated1;
    private double alloc;
    private double alloc_qty;
    private int aq;
    private double balance;
    private String bqery;
    private ResultSet checkAllocQty;
    private Connection con;
    private Connect drv;
    private String fifodate;
    private String iname1;
    private boolean ins;
    private String insertEstimation;
    private String insertIntoAllocation;
    private ResultSet item_date;
    private ResultSet item_date1;
    private ResultSet items;
    private String naa, purchase_id, pur_single_Id, alloc_date1, sdate, sname;
    private double per_rate;
    private double pqty;
    private PreparedStatement pstmt;
    private PreparedStatement pstmt1;
    private PreparedStatement pstmt2;
    private PreparedStatement pstmt3;
    private PreparedStatement pstmt4;
    private PreparedStatement pstmt5;
    private PreparedStatement pstmt6;
    private PreparedStatement pstmt7;
    private double qty;
    private int status;
    private double tot;
    private int updateStatus;
    private String updateStockQuery;
    private long val;
    private double value, rate;
    private Vector vec;
    private boolean updates;
    private String itemCode;
    private String psid;

    public AllocationDao() {
        vec = new Vector();
    }

    public boolean allocate(AllocationBean abean) {

        try {
            con = getConnect();
            con.setAutoCommit(true);
            for (int i = 0; i < abean.getItems().length; i++) {
                // find first purchase of item
                // Quantity to be allocated for ith item
                // String trqty1 = qty.elementAt(i).toString();
                QtyToBeAllocated = Double.parseDouble(abean.getQty()[i]);
                //QtyToBeAllocated1 = Double.parseDouble(abean.getQty()[i]);
                System.out.println("First Stock" + QtyToBeAllocated);
                String source = findFirstSuppStock(abean.getItems()[i], abean.getSiteCode());
                System.out.println("First Stock" + source);
                String[] pick = source.split("@");
                // purchase single id
                itemCode = pick[0];
                // purchase quqntity
                pqty = Double.parseDouble(pick[1]);
                // value of item                
                per_rate = Double.parseDouble(pick[3]);
                //value = (pqty*per_rate);
                purchase_id = pick[4];
                // balance qty
                alloc_qty = Double.parseDouble(pick[5]);
                sdate = pick[6];
                sname = pick[7];
                pqty = pqty - alloc_qty;
                System.out.println("pqty" + pqty);
                System.out.println("alloc_qty" + alloc_qty);
                System.out.println("trqty" + QtyToBeAllocated);
                while ((QtyToBeAllocated > 0) && (pqty > 0)) {
                    System.out.println(pqty + "===" + QtyToBeAllocated);
                    // if allocatable qty is equals to allocatable qty then update stock and workentry
                    if ((pqty == QtyToBeAllocated) && (QtyToBeAllocated > 0) && (pqty > 0)) {
                        value = QtyToBeAllocated * per_rate;

                        ins = insert(abean.getSiteCode(), abean.getItems()[i], pqty, QtyToBeAllocated, abean.getAllocDate(), purchase_id, sdate, sname, per_rate, value, itemCode, pick[8]);
                        if (ins == true) {

                            updates = updateStock(itemCode, pqty, value);
                            if (updates == false) {
                                break;
                            }
                            QtyToBeAllocated = QtyToBeAllocated - pqty;
                            java.math.BigDecimal t0 = new java.math.BigDecimal(QtyToBeAllocated);
                            t0 = t0.setScale(2, java.math.BigDecimal.ROUND_FLOOR);
                            QtyToBeAllocated = t0.doubleValue();
                        }
                    } else if ((pqty < QtyToBeAllocated) && (QtyToBeAllocated > 0) && (pqty > 0)) {
                        double qtyReq = 0.0;
                        // fulfill the available qty
                        System.out.println("value@@@@@@@@@" + value);
                        value = pqty * per_rate;
                        ins = insert(abean.getSiteCode(), abean.getItems()[i], pqty,
                                QtyToBeAllocated, abean.getAllocDate(), purchase_id, sdate, sname, per_rate, value, itemCode, pick[8]);
                        QtyToBeAllocated = (QtyToBeAllocated - pqty);
                        java.math.BigDecimal t1 = new java.math.BigDecimal(QtyToBeAllocated);
                        t1 = t1.setScale(2, java.math.BigDecimal.ROUND_FLOOR);
                        QtyToBeAllocated = t1.doubleValue();
                        if (ins == true) {
                            updates = updateStock(itemCode, pqty, value);
                            if (updates == false) {
                                break;
                            }
                        }
                        // caliculate qty required to be allocated
                        System.out.println("pqty=" + pqty);
                        System.out.println("trqty=" + QtyToBeAllocated);
                        while ((QtyToBeAllocated > 0) && (pqty > 0)) {
                            // find out next available qty to fill above requirement
                            source = findFirstSuppStock(abean.getItems()[i], abean.getSiteCode());
                            String[] pick1 = source.split("@");
                            // purchase single id
                            itemCode = pick1[0];
                            System.out.println(itemCode + "--->");
                            // purchase quqntity
                            pqty = Double.parseDouble(pick1[1]);
                            // value of item
                            // value = Double.parseDouble(pick1[2]);
                            per_rate = Double.parseDouble(pick1[3]);
                            purchase_id = pick1[4];
                            // balance qty
                            alloc_qty = Double.parseDouble(pick[5]);
                            sdate = pick[6];
                            sname = pick[7];
                            System.out.println("pqty===" + pqty);
                            if ((pqty == QtyToBeAllocated) && (QtyToBeAllocated > 0) && (pqty > 0)) {
                                value = (QtyToBeAllocated * per_rate);
                                ins = insert(abean.getSiteCode(), abean.getItems()[i], pqty, QtyToBeAllocated,
                                        abean.getAllocDate(), purchase_id, sdate, sname, per_rate, value, itemCode, pick[8]);
                                if (ins == true) {
                                    updates = updateStock(itemCode, QtyToBeAllocated, value);
                                    QtyToBeAllocated = 0;
                                    if (updates == false) {
                                        break;
                                    }
                                }
                                if (updates == true) {
                                    System.out.println("Qty is equal" + QtyToBeAllocated);
                                    break;
                                }
                            } else if ((pqty > QtyToBeAllocated) && (QtyToBeAllocated > 0) && (pqty > 0)) {
                                System.out.println("Qty------->##@@" + QtyToBeAllocated);
                                value = (QtyToBeAllocated * per_rate);
                                ins = insert(abean.getSiteCode(), abean.getItems()[i], pqty, QtyToBeAllocated,
                                        abean.getAllocDate(), purchase_id, sdate,
                                        sname, per_rate, value, itemCode, pick[8]);
                                if (ins == true) {
                                    updates = updateStock(itemCode, QtyToBeAllocated, value);
                                    QtyToBeAllocated = 0;
                                    System.out.println("End of ---------" + QtyToBeAllocated);
                                    if (updates == false) {
                                        break;
                                    }
                                }
                            } else if ((pqty < QtyToBeAllocated) && (QtyToBeAllocated > 0) && (pqty > 0)) {
                                System.out.println("Start of ---------" + QtyToBeAllocated);
                                QtyToBeAllocated = QtyToBeAllocated - pqty;
                                value = (pqty * per_rate);
                                ins = insert(abean.getSiteCode(), abean.getItems()[i], pqty, QtyToBeAllocated, abean.getAllocDate(),
                                        purchase_id, sdate, sname, per_rate, value, itemCode, pick[8]);
                                if (ins == true) {

                                    updates = updateStock(itemCode, pqty, value);
                                    java.math.BigDecimal t2 = new java.math.BigDecimal(QtyToBeAllocated);
                                    t2 = t2.setScale(2, java.math.BigDecimal.ROUND_FLOOR);
                                    QtyToBeAllocated = t2.doubleValue();
                                    System.out.println("qtyREQ last------->" + QtyToBeAllocated);
                                    if (updates == false) {
                                        break;
                                    }
                                }
                            }
                        } // while end
                    } else if ((pqty > QtyToBeAllocated) && (QtyToBeAllocated > 0) && (pqty > 0)) {
                        System.out.println("Qty available=" + QtyToBeAllocated);
                        System.out.println("Pqty=" + pqty);
                        value = (QtyToBeAllocated * per_rate);
                        ins = insert(abean.getSiteCode(), abean.getItems()[i], pqty, QtyToBeAllocated, abean.getAllocDate(),
                                purchase_id, sdate, sname, per_rate, value, itemCode, pick[8]);
                        if (ins == true) {
                            updates = updateStock(itemCode, QtyToBeAllocated, value);
                            System.out.println("qty------>" + QtyToBeAllocated + " updateed????" + updates);
                            QtyToBeAllocated = 0;
                            if (updates == false) {
                                break;
                            }
                        }
                        if (updates == true) {
                            System.out.println("Qty is equal" + QtyToBeAllocated);
                            break;
                        } // if (pqty<trqty) end
                    }
                } // main while end
            } // for loop end
            if ((QtyToBeAllocated == 0) && (updates == true)) {
                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            Logger.getLogger(AllocationDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;

    }

    public String findFirstSuppStock(String iname, String sitecode) {
        try {
            String firststock = "select item,min(sdate) from stock where item='" + iname + "' and sflag=0 group by item";

            System.out.print(firststock);
            pstmt1 = con.prepareStatement(firststock);
            item_date = pstmt1.executeQuery();

            if (item_date.next() == true) {
                System.out.println("Hello----");
                fifodate = item_date.getString(2);
                System.out.println("Hello----1");
                String sss = "Select pqty,value,per_rate,purchase_id,item_code," +
                        "alloc,sdate,sname,pur_single_Id from stock where item='" + item_date.getString(1) + "' and sdate='" + fifodate + "' and sflag=0";

                System.out.println(sss);
                pstmt2 = con.prepareStatement(sss);
                item_date1 = pstmt2.executeQuery();

                if (item_date1.next() == true) {
                    pqty = item_date1.getDouble(1);
                    value = item_date1.getDouble(2);
                    rate = item_date1.getDouble(3);
                    purchase_id = item_date1.getString(4);
                    itemCode = item_date1.getString(5);
                    alloc_date1 = item_date1.getString(6);
                    sdate = item_date1.getString(7);
                    sname = item_date1.getString(8);
                    psid = item_date1.getString(9);
                }
            }
        } catch (Exception e) {
        }
        System.out.println("111-->" + pur_single_Id + "@" + pqty + "@" + value + "@" + rate + "@" + purchase_id + "@" + alloc + "@" + sdate + "@" + sname + "@");
        return itemCode + "@" + pqty + "@" + value + "@" + rate + "@" + purchase_id + "@" + alloc + "@" + sdate + "@" + sname + "@" + psid + "@";
    }

    public Connection getConnect() throws Exception {
        drv = new Connect();
        con = drv.mySql();
        return con;
    }

    public Vector getItemStock() {
        try {
            con = getConnect();

            String sel = "select item,sum(alloc) from stock where alloc >0 group by item";

            System.out.println(sel);
            pstmt3 = con.prepareStatement(sel);
            items = pstmt3.executeQuery();

            while (items.next()) {
                iname1 = items.getString(1);
                naa = iname1.replaceAll("\"", "&quot;");
                naa = iname1.replaceAll("&", "&amp;");
                naa = iname1.replaceAll("\'", "\\" + "\'");
                alloc = items.getDouble(2);
                vec.add(iname1);
                vec.add(alloc);
            }
        } catch (Exception ex) {
            Logger.getLogger(AllocationDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return vec;
    }

    /**get selected item stock*/
    public double getItemQty(String item) {
        try {
            con = getConnect();

            String sel = "select sum(alloc) from stock where item=? and alloc >0 group by item";

            System.out.println(sel);
            pstmt3 = con.prepareStatement(sel);
            pstmt3.setString(1, item);
            items = pstmt3.executeQuery();

            while (items.next()) {
                qty = items.getDouble(1);
            }
        } catch (Exception ex) {
            Logger.getLogger(AllocationDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return qty;
    }
    //-------

    private boolean insert(String siteCode, String item, double pqty, double QtyToBeAllocated, String allocDate,
            String purchase_id, String sdate, String sname, double per_rate, double value,
            String itemCode, String psid) {
        try {
            insertIntoAllocation = "insert into allocation values('" + siteCode + "','" + item + "'," + QtyToBeAllocated + "," + pqty + ",'" + allocDate + "',0,'" + purchase_id + "','" + sdate + "','" + sname + "',null," + per_rate + "," + value + ",'" + itemCode + "','" + psid + "');";
            pstmt4 = con.prepareStatement(insertIntoAllocation);
            status = pstmt4.executeUpdate();

            if (status >= 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(AllocationDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean updateStock(String itemCode, double qty, double val) {
        try {
            updateStockQuery = "update stock set alloc=alloc-'" + qty + "', value=value-" + val + " where item_code='" + itemCode + "' and sflag=0";
            System.out.print(updateStockQuery);
            pstmt5 = con.prepareStatement(updateStockQuery);
            status = pstmt5.executeUpdate();

            if (status > 0) {
                bqery = "select * from stock where item_code='" + itemCode + "' and alloc=0 and sflag=0";
                System.out.println(bqery);
                pstmt6 = con.prepareStatement(bqery);
                checkAllocQty = pstmt6.executeQuery();

                if (checkAllocQty.next() == true) {
                    String changeflag = "update stock set sflag=1 where item_code='" + itemCode + "'";

                    System.out.print(changeflag);
                    pstmt7 = con.prepareStatement(changeflag);
                    updateStatus = pstmt7.executeUpdate();
                } else {
                    return true;
                }

                if (updateStatus >= 1) {
                    return true;
                } else {
                    return false;
                }
            } else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return false;
    }
}
